import pymysql
import asyncio
import jieba
import pandas as pd
import math

# 打开数据库连接
from aiohttp import ClientSession

db_config = {
    'host': '3a524389i9.qicp.vip',  # 主机
    'user': 'root',  # 用户名
    'password': 'zjdx20210126',  # 密码
    'port': 63307,  # 端口 3306
    'database': 'uni_center'  # 数据库名
}
key_points_df = pd.DataFrame(columns=['datetime', 'value', 'event'])
version = 1

async def main():
    jieba.load_userdict("C:\\Users\\Administrator\\Desktop\\联创接口\\zjdx.txt")
    df = pd.read_excel("C:\\Users\\Administrator\\Desktop\\联创接口\\专家关键词数据2.xlsx")
    db = pymysql.connect(**db_config)
    # 使用cursor()方法获取操作游标a
    cursor = db.cursor()
    ids = pd.to_numeric(df['ID'])
    names = df['姓名']
    word = df['关键词']
    for i, d in ids.items():
        name = names[i]
        user_id = ids[i]
        keys = {}
        if isinstance(word[i], str):
            words = word[i].split(';')
            for w in words:
                # print(f"name -> {name} word -> {w}")
                if len(w) > 2:
                    seg_list = jieba.cut(w, cut_all=False)
                    for seg in seg_list:
                        if len(seg) >= 2:
                            print(f"name -> {name} word -> {seg}")
                            sql = "SELECT book_id FROM word_c_book_20230525 where word = '{}'".format(seg)
                            cursor.execute(sql)
                            # 获取所有记录列表
                            results = cursor.fetchall()
                            for row in results:
                                book_id = row[0]
                                if book_id in keys:
                                    count = keys[book_id]
                                    count = count + 1
                                    keys[book_id] = count
                                else:
                                    keys[book_id] = 1
                else:
                    print(f"name -> {name} word -> {w}")
                    sql = "SELECT book_id FROM word_c_book_20230525 where word = '{}'".format(w)
                    cursor.execute(sql)
                    # 获取所有记录列表
                    results = cursor.fetchall()
                    for row in results:
                        book_id = row[0]
                        if book_id in keys:
                            count = keys[book_id]
                            count = count + 1
                            keys[book_id] = count
                        else:
                            keys[book_id] = 1
        else:
            continue

        for key in keys:
            sqlInsert = 'INSERT INTO recommend_word_c_book_user_20230525(book_id, user_id, user_name, book_name, sort, version) VALUES ({},"{}","{}",(SELECT book_name FROM word_c_book_20230525 where book_id = {} LIMIT 1),"{}","{}");'.format(
                key, user_id, name, key, keys[key], version)
            print(sqlInsert)
            cursor.execute(sqlInsert)
            db.commit()

if __name__ == '__main__':
    loop = asyncio.get_event_loop()
    loop.run_until_complete(main())
